Imports Microsoft.VisualBasic
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.Data
Imports DataAccesGenerales
Imports DataAcces

Public Class DataAccesSol_InfraEstructura
  Private Const consTimeOut As Integer = 0 
  
  Public Function Sol_InfraEstructura_Insertar(pnid As Integer, pnidDocumento As Integer, pvid As Integer, ppDia As String, ppMes As String, ppAnio As String, pnomNaviera As String, pdomicilioNaviera As String, presponsableNaviera As String, prepresentante As String, ppersonalidad As String, pbuque As String, pbandera As String, peslora As String, pTRB As String, pimpo As String, pexpo As String, pimpoCarga As String, pexpoCarga As String, ptonImpo As String, ptonExpo As String, punidImpo As String, punidExpo As String, pETA As String, pHETA As String, ppasajeros As String, pmuelle As String, pproductividad As String, pestadia As String, pETF As String, pHETF As String, pturistico As String, ppuertoFijo As String, ppuertoVariable As String, pcuotaDiaria As String, patraqNoEsp As String, patraqEsp As String, pmuellaje As String, pembarque As String, pmoneda As String, plegales As String, pnidEmpresa As Integer) As Byte
    Dim pTransaccion As Byte = 0
    Dim mensaje As String = String.Empty
    Try
      Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
        Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
          oraCommandDocumentos.Connection = oraConnection
          oraCommandDocumentos.CommandTimeout = consTimeOut
          oraCommandDocumentos.CommandType = CommandType.StoredProcedure
          oraCommandDocumentos.CommandText = "EDIV2.MI_EDIV2GENERALES.Sol_InfraEstructuraInsertar" 
          oraCommandDocumentos.Parameters.Add("pnid", pnid)
          oraCommandDocumentos.Parameters.Add("pnidDocumento", pnidDocumento)
          oraCommandDocumentos.Parameters.Add("pvid", pvid)
          oraCommandDocumentos.Parameters.Add("ppDia", ppDia)
          oraCommandDocumentos.Parameters.Add("ppMes", ppMes)
          oraCommandDocumentos.Parameters.Add("ppAnio", ppAnio)
          oraCommandDocumentos.Parameters.Add("pnomNaviera", pnomNaviera)
          oraCommandDocumentos.Parameters.Add("pdomicilioNaviera", pdomicilioNaviera)
          oraCommandDocumentos.Parameters.Add("presponsableNaviera", presponsableNaviera)
          oraCommandDocumentos.Parameters.Add("prepresentante", prepresentante)
          oraCommandDocumentos.Parameters.Add("ppersonalidad", ppersonalidad)
          oraCommandDocumentos.Parameters.Add("pbuque", pbuque)
          oraCommandDocumentos.Parameters.Add("pbandera", pbandera)
          oraCommandDocumentos.Parameters.Add("peslora", peslora)
          oraCommandDocumentos.Parameters.Add("pTRB", pTRB)
          oraCommandDocumentos.Parameters.Add("pimpo", pimpo)
          oraCommandDocumentos.Parameters.Add("pexpo", pexpo)
          oraCommandDocumentos.Parameters.Add("pimpoCarga", pimpoCarga)
          oraCommandDocumentos.Parameters.Add("pexpoCarga", pexpoCarga)
          oraCommandDocumentos.Parameters.Add("ptonImpo", ptonImpo)
          oraCommandDocumentos.Parameters.Add("ptonExpo", ptonExpo)
          oraCommandDocumentos.Parameters.Add("punidImpo", punidImpo)
          oraCommandDocumentos.Parameters.Add("punidExpo", punidExpo)
          oraCommandDocumentos.Parameters.Add("pETA", pETA)
          oraCommandDocumentos.Parameters.Add("pHETA", pHETA)
          oraCommandDocumentos.Parameters.Add("ppasajeros", ppasajeros)
          oraCommandDocumentos.Parameters.Add("pmuelle", pmuelle)
          oraCommandDocumentos.Parameters.Add("pproductividad", pproductividad)
          oraCommandDocumentos.Parameters.Add("pestadia", pestadia)
          oraCommandDocumentos.Parameters.Add("pETF", pETF)
          oraCommandDocumentos.Parameters.Add("pHETF", pHETF)
          oraCommandDocumentos.Parameters.Add("pturistico", pturistico)
          oraCommandDocumentos.Parameters.Add("ppuertoFijo", ppuertoFijo)
          oraCommandDocumentos.Parameters.Add("ppuertoVariable", ppuertoVariable)
          oraCommandDocumentos.Parameters.Add("pcuotaDiaria", pcuotaDiaria)
          oraCommandDocumentos.Parameters.Add("patraqNoEsp", patraqNoEsp)
          oraCommandDocumentos.Parameters.Add("patraqEsp", patraqEsp)
          oraCommandDocumentos.Parameters.Add("pmuellaje", pmuellaje)
          oraCommandDocumentos.Parameters.Add("pembarque", pembarque)
          oraCommandDocumentos.Parameters.Add("pmoneda", pmoneda)
          oraCommandDocumentos.Parameters.Add("plegales", plegales)
          oraCommandDocumentos.Parameters.Add("pnidEmpresa", pnidEmpresa)

          oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add("pMensaje", Oracle.DataAccess.Client.OracleDbType.Varchar2, 200).Direction = ParameterDirection.Output

          oraConnection.Open()
          oraCommandDocumentos.ExecuteNonQuery()
          pTransaccion = CByte(oraCommandDocumentos.Parameters("pTransaccion").Value.ToString())
          mensaje = oraCommandDocumentos.Parameters("pMensaje").Value.ToString()
          oraConnection.Close()
        End Using
      End Using
    Catch ex As Exception
      Throw ex
    End Try
    Return pTransaccion
  End Function

  Public Function Sol_InfraEstructura_Modificar(pnid As Integer, pnidDocumento As Integer, pvid As Integer, ppDia As String, ppMes As String, ppAnio As String, pnomNaviera As String, pdomicilioNaviera As String, presponsableNaviera As String, prepresentante As String, ppersonalidad As String, pbuque As String, pbandera As String, peslora As String, pTRB As String, pimpo As String, pexpo As String, pimpoCarga As String, pexpoCarga As String, ptonImpo As String, ptonExpo As String, punidImpo As String, punidExpo As String, pETA As String, pHETA As String, ppasajeros As String, pmuelle As String, pproductividad As String, pestadia As String, pETF As String, pHETF As String, pturistico As String, ppuertoFijo As String, ppuertoVariable As String, pcuotaDiaria As String, patraqNoEsp As String, patraqEsp As String, pmuellaje As String, pembarque As String, pmoneda As String, plegales As String, pnidEmpresa As Integer) As Byte
    Dim pTransaccion As Byte = 0
    Dim mensaje As String = String.Empty
    Try
      Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
        Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
          oraCommandDocumentos.Connection = oraConnection
          oraCommandDocumentos.CommandTimeout = consTimeOut
          oraCommandDocumentos.CommandType = CommandType.StoredProcedure
          oraCommandDocumentos.CommandText = "EDIV2.MI_EDIV2GENERALES.Sol_InfraEstructuraModificar"
          oraCommandDocumentos.Parameters.Add("pnid", pnid)
          oraCommandDocumentos.Parameters.Add("pnidDocumento", pnidDocumento)
          oraCommandDocumentos.Parameters.Add("pvid", pvid)
          oraCommandDocumentos.Parameters.Add("ppDia", ppDia)
          oraCommandDocumentos.Parameters.Add("ppMes", ppMes)
          oraCommandDocumentos.Parameters.Add("ppAnio", ppAnio)
          oraCommandDocumentos.Parameters.Add("pnomNaviera", pnomNaviera)
          oraCommandDocumentos.Parameters.Add("pdomicilioNaviera", pdomicilioNaviera)
          oraCommandDocumentos.Parameters.Add("presponsableNaviera", presponsableNaviera)
          oraCommandDocumentos.Parameters.Add("prepresentante", prepresentante)
          oraCommandDocumentos.Parameters.Add("ppersonalidad", ppersonalidad)
          oraCommandDocumentos.Parameters.Add("pbuque", pbuque)
          oraCommandDocumentos.Parameters.Add("pbandera", pbandera)
          oraCommandDocumentos.Parameters.Add("peslora", peslora)
          oraCommandDocumentos.Parameters.Add("pTRB", pTRB)
          oraCommandDocumentos.Parameters.Add("pimpo", pimpo)
          oraCommandDocumentos.Parameters.Add("pexpo", pexpo)
          oraCommandDocumentos.Parameters.Add("pimpoCarga", pimpoCarga)
          oraCommandDocumentos.Parameters.Add("pexpoCarga", pexpoCarga)
          oraCommandDocumentos.Parameters.Add("ptonImpo", ptonImpo)
          oraCommandDocumentos.Parameters.Add("ptonExpo", ptonExpo)
          oraCommandDocumentos.Parameters.Add("punidImpo", punidImpo)
          oraCommandDocumentos.Parameters.Add("punidExpo", punidExpo)
          oraCommandDocumentos.Parameters.Add("pETA", pETA)
          oraCommandDocumentos.Parameters.Add("pHETA", pHETA)
          oraCommandDocumentos.Parameters.Add("ppasajeros", ppasajeros)
          oraCommandDocumentos.Parameters.Add("pmuelle", pmuelle)
          oraCommandDocumentos.Parameters.Add("pproductividad", pproductividad)
          oraCommandDocumentos.Parameters.Add("pestadia", pestadia)
          oraCommandDocumentos.Parameters.Add("pETF", pETF)
          oraCommandDocumentos.Parameters.Add("pHETF", pHETF)
          oraCommandDocumentos.Parameters.Add("pturistico", pturistico)
          oraCommandDocumentos.Parameters.Add("ppuertoFijo", ppuertoFijo)
          oraCommandDocumentos.Parameters.Add("ppuertoVariable", ppuertoVariable)
          oraCommandDocumentos.Parameters.Add("pcuotaDiaria", pcuotaDiaria)
          oraCommandDocumentos.Parameters.Add("patraqNoEsp", patraqNoEsp)
          oraCommandDocumentos.Parameters.Add("patraqEsp", patraqEsp)
          oraCommandDocumentos.Parameters.Add("pmuellaje", pmuellaje)
          oraCommandDocumentos.Parameters.Add("pembarque", pembarque)
          oraCommandDocumentos.Parameters.Add("pmoneda", pmoneda)
          oraCommandDocumentos.Parameters.Add("plegales", plegales)
          oraCommandDocumentos.Parameters.Add("pnidEmpresa", pnidEmpresa)

          oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add("pMensaje", Oracle.DataAccess.Client.OracleDbType.Varchar2, 200).Direction = ParameterDirection.Output

          oraConnection.Open()
          oraCommandDocumentos.ExecuteNonQuery()
          pTransaccion = CByte(oraCommandDocumentos.Parameters("pTransaccion").Value.ToString())
          mensaje = oraCommandDocumentos.Parameters("pMensaje").Value.ToString()
          oraConnection.Close()
        End Using
      End Using
    Catch ex As Exception
      Throw ex
    End Try
    Return pTransaccion
  End Function
  
  Public Function Sol_InfraEstructura_Consultar(nid as Integer) As DataTable
    Dim dtResultado As New DataTable("Sol_InfraEstructuraConsulta")
    Try
      Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
        Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
          oraCommandDocumentos.Connection = oraConnection
          oraCommandDocumentos.CommandTimeout = consTimeOut
          oraCommandDocumentos.CommandType = CommandType.StoredProcedure
          oraCommandDocumentos.CommandText = "EDIV2.MI_EDIV2GENERALES.Sol_InfraEstructuraConsultar" 
          oraCommandDocumentos.Parameters.Add("pnid", nid)
          oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pCursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

          Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
          oraDataAdapter.Fill(dtResultado)
          oraConnection.Close()
        End Using
      End Using
    Catch ex As Exception
      Throw ex
    End Try
    Return dtResultado
  End Function
  
  Public Function Sol_InfraEstructura_Combo(pNombre As String) As DataTable
    Dim dtResultado As New DataTable("Sol_InfraEstructuraCombo")
    Try
      Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
        Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
          oraCommandDocumentos.Connection = oraConnection
          oraCommandDocumentos.CommandTimeout = consTimeOut
          oraCommandDocumentos.CommandType = CommandType.StoredProcedure
          oraCommandDocumentos.CommandText = "EDIV2.MI_EDIV2GENERALES.Sol_InfraEstructuraCombo" 
		  oraCommandDocumentos.Parameters.Add("pNombre", pNombre)
          oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pCursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

          Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
          oraDataAdapter.Fill(dtResultado)
          oraConnection.Close()
        End Using
      End Using
    Catch ex As Exception
      Throw ex
    End Try
    Return dtResultado
  End Function
  
  Public Function Sol_InfraEstructura_CadenaOriginalXMLChilkat(nidDocumento As Integer) As Chilkat.Xml
    Dim detalleDataSet As New DataSet("DatosSol_InfraEstructura")
    Try
      detalleDataSet = Sol_InfraEstructura_DetalleXML(nidDocumento)
	  'En esta parte van las tablas obtenidas en la consulta anterior
	  'separadas dependiendo del reporte a crear, ejemplo:
	  detalleDataSet.Tables(0).TableName = "Informacion del Tramite"
      detalleDataSet.Tables(1).TableName = "Datos Sol_InfraEstructura"
      detalleDataSet.Tables(2).TableName = "Archivos Anexos"
      detalleDataSet.Tables(3).TableName = "Incidencias"

      Dim xml As New Chilkat.Xml
      xml.LoadXml(detalleDataSet.GetXml())
      Return xml 'detalleDataSet.GetXml()
    Catch ex As Exception
      Throw ex
    End Try
  End Function

  Public Function Sol_InfraEstructura_DetalleXML(ByVal nidDocumento As Integer) As DataSet
    Dim dsDetalle As New DataSet("Sol_InfraEstructuraDetalle")
    Dim pTransaccion As Byte = 0
    Try
      Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
        Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
          oraCommandDocumentos.Connection = oraConnection
          oraCommandDocumentos.CommandTimeout = consTimeOut
          oraCommandDocumentos.CommandType = CommandType.StoredProcedure
          oraCommandDocumentos.CommandText = "EDIV2.MI_EDIV2GENERALES.Sol_InfraEstructuraDetalleXML"
          oraCommandDocumentos.Parameters.Add("pNidDocumento", nidDocumento)
          oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
          'En esta parte es para los cursores necesario 
		  oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pEncabezado", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pDatos", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pAnexos", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pIncidencias", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
		
          Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
          'pTransaccion = CByte(oraCommandDocumentos.Parameters("pTransaccion").Value.ToString())
          oraDataAdapter.Fill(dsDetalle)
        End Using
      End Using
    Catch ex As Exception
      Throw ex
    End Try
    Return dsDetalle
  End Function
  
  Public Function Sol_InfraEstructura_Borrar(pnid As Integer) As Byte
    Dim pTransaccion As Byte = 0
    Dim mensaje As String = String.Empty
    Try
      Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
        Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
          oraCommandDocumentos.Connection = oraConnection
          oraCommandDocumentos.CommandTimeout = consTimeOut
          oraCommandDocumentos.CommandType = CommandType.StoredProcedure
          oraCommandDocumentos.CommandText = "EDIV2.MI_EDIV2GENERALES.Sol_InfraEstructuraBorrar"
          oraCommandDocumentos.Parameters.Add("pnid", pnid)
          oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
          
          oraConnection.Open()
          oraCommandDocumentos.ExecuteNonQuery()
          pTransaccion = CByte(oraCommandDocumentos.Parameters("pTransaccion").Value.ToString())
          oraConnection.Close()
        End Using
      End Using
    Catch ex As Exception
      Throw ex
    End Try
    Return pTransaccion
  End Function
  
End Class
